﻿using QuanLyGiaoVuDAL.ADOWrapper;
using QuanLyGiaoVuDAL.Mapper;
using QuanLyGiaoVuDAL.DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace QuanLyGiaoVuDAL.Repositories.DatabaseRepositories
{
    public class TietHocRepository : ITietHocRepository
    {

        public bool DeleteTietHoc(int maTietHoc)
        {
            return StoreProcedureExecutor.ExecuteNonQueryStoredProcedure("[dbo].[usp_TietHocDelete]", DBParameterFactory.Create("MaTiet",maTietHoc));
        }

        public IEnumerable<TietHocDTO> GetAllTietHoc(int? tiet, int? ca, bool? buoi)
        {
            List<TietHocDTO> tietHocList = new List<TietHocDTO>();
            var maTietParameter = DBParameterFactory.Create("MaTiet", SqlDbType.Int, DBNull.Value);
            var tietParameter = tiet != null ? DBParameterFactory.Create("Tiet", SqlDbType.Int, tiet) : DBParameterFactory.Create("Tiet", SqlDbType.Int, DBNull.Value);
            var caParameter = ca != null ? DBParameterFactory.Create("Ca", SqlDbType.Int, ca) : DBParameterFactory.Create("Ca", SqlDbType.Int, DBNull.Value);
            var buoiParameter = buoi != null ? DBParameterFactory.Create("Buoi", SqlDbType.Int, buoi) : DBParameterFactory.Create("Buoi", SqlDbType.Int, DBNull.Value);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_TietHocSelect]", maTietParameter, tietParameter, caParameter, buoiParameter);
            var tietHocTable = dbResult.Tables[0].Rows;
            foreach (DataRow dataRow in tietHocTable)
            {
                tietHocList.Add(dataRow.ToTietHocDTO());
            }
            return tietHocList.AsEnumerable();
        }

        public DTO.TietHocDTO GetTietHocByMaTietHoc(int maTietHoc)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_TietHocSelect]", DBParameterFactory.Create("MaTiet", maTietHoc));
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToTietHocDTO();
        }

        public DTO.TietHocDTO InsertTietHoc(DTO.TietHocDTO tietHocDTO)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_TietHocInsert]", GetParameterArray(tietHocDTO));
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToTietHocDTO();
        }

        public DTO.TietHocDTO UpdateTietHoc(DTO.TietHocDTO tietHocDTO)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_TietHocUpdate]", GetParameterArray(tietHocDTO));
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToTietHocDTO();
        }

        #region Private methods
        private SqlParameter[] GetParameterArray(TietHocDTO tietHocDTO)
        {
            SqlParameter[] parameters = new SqlParameter[6];
            parameters[0] = DBParameterFactory.Create("MaTiet", tietHocDTO.MaTiet);
            parameters[1] = DBParameterFactory.Create("Tiet", tietHocDTO.TietThu);
            parameters[2] = DBParameterFactory.Create("GioBatDau", tietHocDTO.GioBatDau);
            parameters[3] = DBParameterFactory.Create("GioKetThuc", tietHocDTO.GioKetThuc);
            parameters[4] = tietHocDTO.Ca != null ? DBParameterFactory.Create("Ca", tietHocDTO.Ca) : DBParameterFactory.Create("Ca", SqlDbType.Int, DBNull.Value);
            parameters[5] = DBParameterFactory.Create("Buoi", tietHocDTO.Buoi);
            return parameters;
        }
        #endregion


        public IEnumerable<int> GetAllCa()
        {
            List<int> caList = new List<int>();
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_TietHocLayDanhSachCa]");
            var caTable = dbResult.Tables[0].Rows;
            foreach (DataRow dataRow in caTable)
            {
                caList.Add(Convert.ToInt32(dataRow["ca"]));
            }
            return caList.AsEnumerable();
        }
    }
}
